package simple.xa;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Blob;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import javax.sql.DataSource;
import javax.transaction.Status;
import javax.transaction.UserTransaction;

import org.apache.commons.codec.net.BCodec;
import org.apache.log4j.Logger;
import org.postgresql.largeobject.LargeObject;
import org.postgresql.largeobject.LargeObjectManager;

import junit.framework.Assert;

import com.atomikos.icatch.jta.UserTransactionImp;
import com.atomikos.jdbc.AtomikosDataSourceBean;

public class XaTransport {
	// the globally unique resource name for the DB; change if needed
	private static String resourceNameRemote = "samples/jdbc/remotePgSQLJdbcPooledDatabase";
	private static String resourceNameLocal = "samples/jdbc/localDerbySQLJdbcPooledDatabase";
	private static Logger logger = Logger.getLogger(XaTransport.class);

	/**
	 * Instance of org.apache.commons.codec.net.BCodec for encoding any String
	 * that is supposed to be stored in the DB.
	 */
	private static BCodec myBCodec = new BCodec();

	private static AtomikosDataSourceBean remoteDs = null;

	private static AtomikosDataSourceBean localDs = null;

	public static final String LOCAL_FILE_INSERT = "INSERT INTO File (f_name, f_description, f_data) "
			+ "VALUES (?, ?, ?)";

	public static final String REMOTE_FILE_INSERT = "INSERT INTO File (f_name, f_description, f_oid) VALUES (?, ?, ?)";

	public static final String LOCAL_FILE_SELECT_DATA = "SELECT f_data FROM File WHERE f_id = %d";

	public static final String REMOTE_FILE_SELECT_DATA = "SELECT f_oid FROM File WHERE f_id = %d";

	public static final String FILE_SELECT_NAME = "SELECT f_name FROM File WHERE f_id = %d";

	public static final String FILE_SELECT_DESC = "SELECT f_description FROM File WHERE f_id = %d";

	public static final String FILE_SELECT_ALL = "SELECT * FROM File WHERE f_id = %d";

	public static DataSource getRemoteDataSource() {

		if (remoteDs == null) {
			// Find or construct a datasource instance;
			// this could equally well be a JNDI lookup
			// where available. To keep it simple, this
			// demo merely constructs a new instance.
			remoteDs = new AtomikosDataSourceBean();
			// org.apache.derby.jdbc.EmbeddedXADataSource derbyDs = new
			// org.apache.derby.jdbc.EmbeddedXADataSource();

			org.postgresql.xa.PGXADataSource pgSqlDs = new org.postgresql.xa.PGXADataSource();
			pgSqlDs.setDatabaseName("SilverTest");
			// build.setCreateDatabase("/Users/yudi/Desktop/derbyDb/test");

			pgSqlDs.setUser("yudi");
			pgSqlDs.setPassword("macjaguar");
			pgSqlDs.setServerName("localhost");
			pgSqlDs.setPortNumber(5433);

			Assert.assertNotNull(pgSqlDs);

			remoteDs.setXaDataSource(pgSqlDs);

			// REQUIRED: unique resource name for transaction recovery
			// configuration
			remoteDs.setUniqueResourceName(resourceNameRemote);
			// OPTIONAL: what is the pool size?
			remoteDs.setPoolSize(10);
			// OPTIONAL: how long until the pool thread checks liveness of
			// connections?
			remoteDs.setBorrowConnectionTimeout(100);

			// NOTE: the resulting datasource can be bound in JNDI where
			// available
		}
		return remoteDs;
	}

	public static DataSource getLocalDataSource() {

		if (localDs == null) {
			// Find or construct a datasource instance;
			// this could equally well be a JNDI lookup
			// where available. To keep it simple, this
			// demo merely constructs a new instance.
			localDs = new AtomikosDataSourceBean();
			org.apache.derby.jdbc.EmbeddedXADataSource derbyDs = new org.apache.derby.jdbc.EmbeddedXADataSource();

			// build.setCreateDatabase("/Users/yudi/Desktop/derbyDb/test");
			derbyDs.setCreateDatabase("create");
			derbyDs.setDatabaseName("/Users/yudi/Desktop/derbyDb/test/test");
			Assert.assertNotNull(derbyDs);

			localDs.setXaDataSource(derbyDs);
			// REQUIRED: unique resource name for transaction recovery
			// configuration
			localDs.setUniqueResourceName(resourceNameLocal);
			// OPTIONAL: what is the pool size?
			localDs.setPoolSize(10);
			// OPTIONAL: how long until the pool thread checks liveness of
			// connections?
			localDs.setBorrowConnectionTimeout(100);

			// NOTE: the resulting datasource can be bound in JNDI where
			// available
		}
		return localDs;
	}

	public static void startTransaction() throws Exception {
		logger.info(0);
		// Retrieve of construct the UserTransaction
		// (can be bound in JNDI where available)
		UserTransaction utx = new UserTransactionImp();
		utx.setTransactionTimeout(60);

		// First, create a transaction
		utx.begin();
	}

	public static void closeTransaction(boolean error) throws Exception {
		logger.info(1);
		UserTransaction utx = new UserTransactionImp();
		if (utx.getStatus() != Status.STATUS_NO_TRANSACTION) {
			if (error)
				utx.rollback();
			else
				utx.commit();
		} else
			logger.info("WARNING: closeConnection called outside a tx");
	}

	/**
	 * Utility method to start a transaction and get a connection.
	 * 
	 * @return Connection The connection.
	 */
	public static Connection getRemoteConnection() throws Exception {
		DataSource ds = getRemoteDataSource();
		Connection conn = null;
		conn = ds.getConnection();

		return conn;
	}

	/**
	 * Utility method to start a transaction and get a connection.
	 * 
	 * @return Connection The connection.
	 */
	public static Connection getLocalConnection() throws Exception {
		DataSource ds = getLocalDataSource();
		Connection conn = null;
		conn = ds.getConnection();
		return conn;
	}

	/**
	 * Utility method to close the connection and terminate the transaction.
	 * This method does all XA related tasks and should be called within a
	 * transaction. When it returns, the transaction will be terminated.
	 * 
	 * @param conn
	 *            The connection.
	 * @param error
	 *            Indicates if an error has occurred or not. If true, the
	 *            transaction will be rolled back. If false, the transaction
	 *            will be committed.
	 */

	public static void closeConnection(Connection conn) throws Exception {
		if (conn != null)
			conn.close();
	}

	/**
	 * Updated for Large Object Descriptor (OID type) in PostgreSQL It will
	 * never overload the server memory loading large objects (file).
	 * 
	 * @param fName
	 * @param fDescr
	 * @param fileSourceLocation
	 * @return
	 * @throws Exception
	 */
	public static int addFileToLocalStorage(String fName, String fDescr,
			String fileSourceLocation) throws Exception {
		String sqlStmt = LOCAL_FILE_INSERT;
		// FILE_INSERT =
		// "INSERT INTO File (f_name, f_description, f_data) VALUES (?, ?, ?)";
		String fDescEnc = myBCodec.encode(fDescr);
		String fNameEnc = myBCodec.encode(fName);
		boolean error = false;
		int key = -1;

		File f = new File(fileSourceLocation);
		if (!f.exists()) {
			error = true;
			throw new IOException(
					"Attempting to save file to DB - File not found: "
							+ fileSourceLocation);
		}
		FileInputStream in = null;

		Connection conn = null;
		PreparedStatement insStmt = null;
		// Create Connection and execute SQL
		try {
			in = new FileInputStream(f);
			byte[] file = new byte[(int) f.length()];
			in.read(file);
			conn = getLocalConnection();
			startTransaction();

			insStmt = conn.prepareStatement(sqlStmt,
					Statement.RETURN_GENERATED_KEYS);
			insStmt.setString(1, fNameEnc);
			// TODO - this could also be
			// f.getName()
			insStmt.setString(2, fDescEnc);
			insStmt.setBytes(3, file);
			insStmt.executeUpdate();

			// Retrieve the auto generated key(s).
			ResultSet rs = insStmt.getGeneratedKeys();
			if (rs == null) {
				// error = true;
				throw new SQLException("error getting generated key");
			}
			if (rs.next()) {
				key = rs.getInt(1);
			} else
				logger.info("no resultset for getGeneratedKeys");

			// Close statement and Connection
			insStmt.close();

			// logger.info(">>> Generated file key = " + key);
			// return the generated key value
		} catch (SQLException sqlExcept) {
			error = true;

			throw sqlExcept;
		} catch (Exception except) {
			error = true;
			//
			throw except;
		} finally {
			try {
				in.close();
				closeConnection(conn);
				closeTransaction(error);
			} catch (Exception e) {
				error = true;
				throw new Exception("rollback failed in addFile");
			}
		}

		// Default return -1 if adding was unsuccessful
		return (!error) ? key : -1;
	}

	public static int addFileToRemoteStorage(String fName, String fDescr,
			String fileSourceLocation) throws Exception {
		String sqlStmt = REMOTE_FILE_INSERT;
		// FILE_INSERT =
		// "INSERT INTO File (f_name, f_description, f_data) VALUES (?, ?, ?)";
		String fDescEnc = myBCodec.encode(fDescr);
		String fNameEnc = myBCodec.encode(fName);
		boolean error = false;
		int key = -1;

		Connection conn = null;
		PreparedStatement insStmt = null;
		// Create Connection and execute SQL
		try {
			conn = getRemoteConnection();
			conn.setAutoCommit(false);
			startTransaction();

			// Get the Large Object Manager to perform operations with
			LargeObjectManager lobj = ((org.postgresql.PGConnection) conn)
					.getLargeObjectAPI();

			// Create a new large object
			long oid = lobj.createLO(LargeObjectManager.READ
					| LargeObjectManager.WRITE);

			// Open the large object for writing
			LargeObject obj = lobj.open(oid, LargeObjectManager.WRITE);

			// Now open the file
			File file = new File(fileSourceLocation);
			FileInputStream fis = new FileInputStream(file);

			// Copy the data from the file to the large object
			byte buf[] = new byte[2048];
			int s, tl = 0;
			while ((s = fis.read(buf, 0, 2048)) > 0) {
				obj.write(buf, 0, s);
				tl += s;
			}

			// Close the large object
			obj.close();

			// Now insert the row into imageslo
			insStmt = conn.prepareStatement(sqlStmt,
					Statement.RETURN_GENERATED_KEYS);
			insStmt.setString(1, fNameEnc);
			insStmt.setString(2, fDescEnc);
			insStmt.setLong(3, oid);
			insStmt.executeUpdate();
			ResultSet rs = insStmt.getGeneratedKeys();
			if (rs == null) {
				error = true;
				throw new SQLException(
						"Adding File failed, no generated key obtained.");
			}
			if (rs.next()) {
				key = rs.getInt(1);
			} else
				logger.info("no resultset for getGeneratedKeys");

			fis.close();
			insStmt.close();

		} catch (SQLException sqlExcept) {
			error = true;

			throw sqlExcept;
		} catch (Exception except) {
			error = true;
			//
			throw except;
		} finally {
			try {
				closeConnection(conn);
				closeTransaction(error);
			} catch (Exception e) {
				error = true;
				throw new Exception("rollback failed in addFile");
			}
		}

		// Default return -1 if adding was unsuccessful
		return (!error) ? key : -1;
	}

	public static void initRemoteTables() throws Exception {
		boolean error = false;
		Connection _remoteConn = null;
		try {
			startTransaction();
			_remoteConn = getRemoteConnection();
			Statement _s = _remoteConn.createStatement();

			// _s.executeUpdate("CREATE TABLE File ( "
			// + "f_ID			INT 			GENERATED ALWAYS AS IDENTITY,"
			// + "f_name			VARCHAR(1000)	NOT NULL, "
			// + "f_description	VARCHAR(1000), " + "f_data			BLOB(1G), "
			// + "CONSTRAINT f_pkey PRIMARY KEY(f_ID)" + ")");
			_s.executeUpdate("CREATE TABLE File ("
					+ "	f_ID			SERIAL PRIMARY KEY,"
					+ "	f_name			VARCHAR(128)	NOT NULL,"
					+ "	f_description	VARCHAR(1000)," + "	f_oid			oid);");

			_s.close();
		} catch (Exception e) {
			error = true;
			throw e;
		} finally {
			closeConnection(_remoteConn);
			closeTransaction(error);
		}
		// That concludes setup
	}

	public static void initLocalTables() throws Exception {
		boolean error = false;
		Connection _localConn = null;
		try {
			startTransaction();
			_localConn = getLocalConnection();
			Statement _s = _localConn.createStatement();

			_s.executeUpdate("CREATE TABLE File ( "
					+ "f_ID			INT 			GENERATED BY DEFAULT AS IDENTITY,"
					+ "f_name			VARCHAR(1000)	NOT NULL, "
					+ "f_description	VARCHAR(1000), " + "f_data			BLOB(1G), "
					+ "CONSTRAINT f_pkey PRIMARY KEY(f_ID)" + ")");

			_s.close();
		} catch (Exception e) {
			error = true;
			throw e;
		} finally {
			closeConnection(_localConn);
			closeTransaction(error);
		}

		// That concludes setup
	}

	public static void dropLocalTables() throws Exception {

		Connection conn = null;
		Statement stmt = null;
		boolean error = false;

		try {
			startTransaction();
			conn = getLocalConnection();
			stmt = conn.createStatement();
			stmt.execute("DROP TABLE File");
			stmt.close();

		} catch (Exception e) {
			error = true;
			throw e;
		} finally {
			try {
				closeConnection(conn);
				closeTransaction(error);
			} catch (Exception e) {
				error = true;
				throw e;
			}
		}

	}

	public static void dropRemoteTables() throws Exception {
		Connection conn = null;
		Statement stmt = null;
		boolean error = false;

		try {
			startTransaction();
			conn = getRemoteConnection();
			stmt = conn.createStatement();
			stmt.execute("DROP TABLE File");
			stmt.close();

		} catch (Exception e) {
			error = true;
			throw e;
		} finally {
			try {
				closeConnection(conn);
				closeTransaction(error);
			} catch (Exception e) {
				error = true;
				throw e;
			}
		}

	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see edu.usask.cs.silver.persistence.Storage#getFName(int)
	 */
	public static String getFNamefromLocal(int fId) throws Exception {
		String sqlStmt;
		sqlStmt = String.format(FILE_SELECT_NAME, fId);
		return executeLocalSqlReturnString(sqlStmt);
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see edu.usask.cs.silver.persistence.Storage#getFName(int)
	 */
	public static String getFNamefromRemote(int fId) throws Exception {
		String sqlStmt;
		sqlStmt = String.format(FILE_SELECT_NAME, fId);
		return executeRemoteSqlReturnString(sqlStmt);
	}

	public static void getRemoteFFileToLocalStorage(int fId, File destination)
			throws Exception {
		String sqlStmt;
		// FILE_SELECT_DATA = "SELECT f_data FROM File WHERE f_id = %d";
		sqlStmt = String.format(REMOTE_FILE_SELECT_DATA, fId);
		boolean error = false;
		Connection conn = null;

		try {
			conn = getRemoteConnection();
			startTransaction();
			// All LargeObject API calls must be within a transaction block
			conn.setAutoCommit(false);

			// Get the Large Object Manager to perform operations with
			LargeObjectManager lobj = ((org.postgresql.PGConnection) conn)
					.getLargeObjectAPI();

			// PreparedStatement ps = conn.prepareStatement(sqlStmt);
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(sqlStmt);
			// ps.setInt(1, fId);
			// ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				// Open the large object for reading
				long oid = rs.getLong(1);
				LargeObject obj = lobj.open(oid, LargeObjectManager.READ);
				// Read the data
				byte buf[] = new byte[obj.size()];
				obj.read(buf, 0, obj.size());
				// Do something with the data read here

				FileOutputStream output = new FileOutputStream(destination);
				output.write(buf);
				// output.write(outFile.getBytes(1, (int) outFile.length()));
				output.flush();
				output.close();

				// Close the object
				obj.close();
			}
			rs.close();
			stmt.close();

			logger.info("SAVED: " + destination.getAbsolutePath());

		} catch (Exception except) {

			error = true;
			throw except;
		} finally {
			try {
				closeConnection(conn);
				closeTransaction(error);
			} catch (Exception e) {
				throw new Exception("roll back failed in getFileToLocalStorage");
			}
		}

	}

	public static void getLocalFFileToLocalStorage(int fId, File destination)
			throws Exception {
		String sqlStmt;
		// FILE_SELECT_DATA = "SELECT f_data FROM File WHERE f_id = %d";
		sqlStmt = String.format(LOCAL_FILE_SELECT_DATA, fId);
		boolean error = false;
		Connection conn = null;
		Statement stmt = null;
		// Create Connection and execute SQL
		try {
			startTransaction();
			conn = getLocalConnection();

			ResultSet rset;
			Blob outFile;
			stmt = conn.createStatement();
			rset = stmt.executeQuery(sqlStmt);
			rset.next();
			outFile = rset.getBlob("f_data");

			rset.close();
			stmt.close();

			FileOutputStream output = new FileOutputStream(destination);
			output.write(outFile.getBytes(1, (int) outFile.length()));
			output.flush();
			output.close();

			logger.info("SAVED: " + destination.getAbsolutePath());

		} catch (Exception except) {

			error = true;
			throw except;
		} finally {
			try {
				closeConnection(conn);
				closeTransaction(error);
			} catch (Exception e) {
				throw new Exception("roll back failed in getFileToLocalStorage");
			}
		}
	}

	public static int getFFileFromLocalStorageToRemoteStorage(int fId)
			throws Exception {
		String sqlStmt, sqlStmt3;
		// FILE_SELECT_DATA = "SELECT f_data FROM File WHERE f_id = %d";
		sqlStmt = String.format(FILE_SELECT_ALL, fId);

		sqlStmt3 = REMOTE_FILE_INSERT;

		boolean error = false;
		Connection localConn = null;
		Connection remoteConn = null;
		Statement stmt = null;
		PreparedStatement insStmt = null;

		int key = -1;

		// Create Connection and execute SQL
		try {
			localConn = getLocalConnection();
			localConn.setAutoCommit(false);
			remoteConn = getRemoteConnection();
			remoteConn.setAutoCommit(false);

			startTransaction();

			stmt = localConn.createStatement();
			ResultSet rs = stmt.executeQuery(sqlStmt);
			rs.next();
			String fNameEnc = null;
			String fDescEnc = null;
			fNameEnc = rs.getString("f_name");
			fDescEnc = rs.getString("f_description");
			Blob outFile;
			outFile = rs.getBlob("f_data");

			// Get the Large Object Manager to perform operations with
			LargeObjectManager lobj = ((org.postgresql.PGConnection) remoteConn)
					.getLargeObjectAPI();

			// Create a new large object
			long oid = lobj.createLO(LargeObjectManager.READ
					| LargeObjectManager.WRITE);

			// Open the large object for writing
			LargeObject obj = lobj.open(oid, LargeObjectManager.WRITE);

			InputStream stream = outFile.getBinaryStream();
			byte buf[] = new byte[2048];
			int s, tl = 0;
			while ((s = stream.read(buf, 0, 2048)) > 0) {
				obj.write(buf, 0, s);
				tl += s;
			}

			obj.close();
			// Now insert the row into imageslo
			insStmt = remoteConn.prepareStatement(sqlStmt3,
					Statement.RETURN_GENERATED_KEYS);
			insStmt.setString(1, fNameEnc);
			insStmt.setString(2, fDescEnc);
			insStmt.setLong(3, oid);
			insStmt.executeUpdate();
			rs = insStmt.getGeneratedKeys();
			if (rs == null) {
				error = true;
				throw new SQLException(
						"Adding File failed, no generated key obtained.");
			}
			if (rs.next()) {
				key = rs.getInt(1);
			} else
				logger.info("no resultset for getGeneratedKeys");

			stream.close();
			rs.close();
			stmt.close();
			insStmt.close();

		} catch (Exception except) {

			error = true;
			throw except;
		} finally {
			try {
				closeConnection(localConn);
				closeConnection(remoteConn);
				closeTransaction(error);
			} catch (Exception e) {
				throw new Exception("roll back failed in getFileToLocalStorage");
			}
		}
		return (!error) ? key : -1;
	}

	public static int getFFileFromRemoteStorageToLocalStorage(int fId)
			throws Exception {
		String sqlStmt, sqlStmt3;
		// FILE_SELECT_DATA = "SELECT f_data FROM File WHERE f_id = %d";
		sqlStmt = String.format(FILE_SELECT_ALL, fId);

		sqlStmt3 = LOCAL_FILE_INSERT;

		boolean error = false;
		Connection localConn = null;
		Connection remoteConn = null;
		Statement stmt = null;
		PreparedStatement insStmt = null;

		int key = -1;

		// Create Connection and execute SQL
		try {
			remoteConn = getRemoteConnection();
			localConn = getLocalConnection();
			remoteConn.setAutoCommit(false);
			localConn.setAutoCommit(false);
			startTransaction();

			// Get the Large Object Manager to perform operations with
			LargeObjectManager lobj = ((org.postgresql.PGConnection) remoteConn)
					.getLargeObjectAPI();

			// PreparedStatement ps = conn.prepareStatement(sqlStmt);
			stmt = remoteConn.createStatement();
			ResultSet rs = stmt.executeQuery(sqlStmt);
			// ps.setInt(1, fId);
			// ResultSet rs = ps.executeQuery();
			String fNameEnc = null;
			String fDescEnc = null;
			byte buf[] = null;
			while (rs.next()) {
				fNameEnc = rs.getString("f_name");
				fDescEnc = rs.getString("f_description");

				// Open the large object for reading
				long oid = rs.getLong("f_oid");
				LargeObject obj = lobj.open(oid, LargeObjectManager.READ);
				// Read the data
				buf = new byte[obj.size()];
				obj.read(buf, 0, obj.size());
				// Do something with the data read here

				// Close the object
				obj.close();
			}

			// Now insert the row into imageslo
			insStmt = localConn.prepareStatement(sqlStmt3,
					Statement.RETURN_GENERATED_KEYS);
			insStmt.setString(1, fNameEnc);
			// TODO - this could also be
			// f.getName()
			insStmt.setString(2, fDescEnc);
			insStmt.setBytes(3, buf);
			insStmt.executeUpdate();

			// Retrieve the auto generated key(s).
			rs = insStmt.getGeneratedKeys();
			if (rs == null) {
				// error = true;
				throw new SQLException("error getting generated key");
			}
			if (rs.next()) {
				key = rs.getInt(1);
			} else
				logger.info("no resultset for getGeneratedKeys");

			rs.close();
			stmt.close();
			insStmt.close();

		} catch (Exception except) {

			error = true;
			throw except;
		} finally {
			try {
				closeConnection(localConn);
				closeConnection(remoteConn);
				closeTransaction(error);
			} catch (Exception e) {
				throw new Exception("roll back failed in getFileToLocalStorage");
			}
		}
		return (!error) ? key : -1;
	}

	private static String executeLocalSqlReturnString(String sqlStmt)
			throws Exception {
		Connection conn = null;
		Statement stmt = null;
		String retVal = null;
		String decoded = null;
		boolean error = false;

		// Create Connection and execute SQL
		try {
			startTransaction();
			conn = getLocalConnection();
			stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(sqlStmt);

			// Extract the String value from the result set
			if (rs != null) {
				try {
					// TODO - consider checking to make sure there's only a
					// single row in rs
					while (rs.next()) {
						try {
							retVal = rs.getString(1);
						} catch (SQLException e) {
							error = true;
							throw e;
						}
					}
					// DECODE THE STRING
					decoded = myBCodec.decode(retVal);
				} catch (SQLException e) {
					error = true;
					throw e;
				}
			}

			// Close statement and Connection
			stmt.close();
		} catch (SQLException sqlExcept) {

			error = true;
			throw sqlExcept;
		} catch (Exception except) {

			error = true;
			throw except;
		} finally {
			try {
				closeConnection(conn);
				closeTransaction(error);
			} catch (Exception e) {
				throw e;
			}
		}

		return decoded;
	}

	private static String executeRemoteSqlReturnString(String sqlStmt)
			throws Exception {
		Connection conn = null;
		Statement stmt = null;
		String retVal = null;
		String decoded = null;
		boolean error = false;

		// Create Connection and execute SQL
		try {
			conn = getRemoteConnection();

			startTransaction();

			stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(sqlStmt);

			// Extract the String value from the result set
			if (rs != null) {
				try {
					// TODO - consider checking to make sure there's only a
					// single row in rs
					while (rs.next()) {
						try {
							retVal = rs.getString(1);
						} catch (SQLException e) {
							error = true;
							throw e;
						}
					}
					// DECODE THE STRING
					decoded = myBCodec.decode(retVal);
				} catch (SQLException e) {
					error = true;
					throw e;
				}
			}

			// Close statement and Connection
			stmt.close();
		} catch (SQLException sqlExcept) {

			error = true;
			throw sqlExcept;
		} catch (Exception except) {

			error = true;
			throw except;
		} finally {
			try {
				closeConnection(conn);
				closeTransaction(error);
			} catch (Exception e) {
				throw e;
			}
		}

		return (!error) ? decoded : null;
	}

}
